﻿using System;
using System.Collections.Generic;
using System.Text;
using StockTrader.DAL.IDAL.HnxInfo;
using StockTrader.BusinessService.DataContract.HnxInfo;
using MySql.Data.MySqlClient;
using System.Data;
using StockTrader.DAL.MySQLHelper;

namespace StockTrader.DAL.MySQL.HnxInfo
{
    public class HnxStock : IHnxStock
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        #region Parameters

        private const string PARM_STOCK_ID = "@STOCKID";
        private const string PARM_CODE = "@CODE";
        private const string PARM_TOTAL_LISTING_QTTY = "@TOTALLISTING";
        private const string PARM_REFERENCE_STATUS = "@REFERENCESTATUS";
        private const string PARM_PRIOR_CLOSE_PRICE = "@PRIORCLOSEPRICE";
        private const string PARM_STATUS = "@STATUS";

        private const string PARM_OPEN_PRICE = "@OPEN";
        private const string PARM_CLOSE_PRICE = "@CLOSE";
        private const string PARM_CURRENT_PRICE = "@PRICE";
        private const string PARM_CURRENT_QTTY = "@QTTY";
        private const string PARM_HIGHEST_PRICE = "@HIGH";
        private const string PARM_LOWEST_PRICE = "@LOW";
        private const string PARM_BEST_OFFER_PRICE = "@BESTOFFER";
        private const string PARM_BEST_BID_PRICE = "@BESTBID";
        private const string PARM_CEILING_PRICE = "@CEILING";
        private const string PARM_FLOOR_PRICE = "@FLOOR";
        private const string PARM_TOTAL_OFFER_QTTY = "@TOTALOFFER";
        private const string PARM_TOTAL_BID_QTTY = "@TOTALBID";
        private const string PARM_BEST_OFFER_QTTY = "@BESTOFFERQTTY";
        private const string PARM_BEST_BID_QTTY = "@BESTBIDQTTY";
        private const string PARM_REF = "@REF";
        private const string PARM_MATCH_PRICE = "@MATCH";
        private const string PARM_MATCH_QTTY = "@MATCHQTTY";
        private const string PARM_OFFER_COUNT = "@OFFERCOUNT";
        private const string PARM_BID_COUNT = "@BIDCOUNT";
        private const string PARM_AVERAGE_PRICE = "@AVERAGE";
        private const string PARM_NM_TOTAL_TRADED_QTTY = "@TOTALTRADED";
        private const string PARM_NM_TOTAL_TRADED_VALUE = "@TOTALTRADEDVALUE";
        private const string PARM_PT_MATCH_QTTY = "@PTMATCHQTTY";
        private const string PARM_PT_MATCH_PRICE = "@PTMATCH";
        private const string PARM_PT_TOTAL_TRADED_QTTY = "@PTTOTALTRADED";
        private const string PARM_PT_TOTAL_TRADED_VALUE = "@PTTOTALTRADEDVALUE";
        private const string PARM_TOTAL_BUY_TRADING_QTTY = "@BUYTRADINGQTTY";
        private const string PARM_BUY_COUNT = "@BUYCOUNT";
        private const string PARM_TOTAL_BUY_TRADING_VALUE = "@BUYTRADINGVALUE";
        private const string PARM_TOTAL_SELL_TRADING_QTTY = "@SELLTRADINGQTTY";
        private const string PARM_SELL_COUNT = "@SELLCOUNT";
        private const string PARM_TOTAL_SELL_TRADING_VALUE = "@SELLTRADINGVALUE";
        private const string PARM_TOTAL_TRADING_QTTY = "@TOTALTRADING";
        private const string PARM_TOTAL_TRADING_VALUE = "@TOTALTRADINGVALUE";
        private const string PARM_BUY_FOREIGN_QTTY = "@FBUY";
        private const string PARM_BUY_FOREIGN_VALUE = "@FBUYVALUE";
        private const string PARM_SELL_FOREIGN_QTTY = "@FSELL";
        private const string PARM_SELL_FOREIGN_VALUE = "@FSELLVALUE";
        private const string PARM_REMAIN_FOREIGN_QTTY = "@FREMAIN";
        private const string PARM_CURRENT_ROOM = "@FROOM";
        private const string PARM_DATE_NO = "@DATENO";
        private const string PARM_TRADING_DATE = "@TRADINGDATE";
        private const string PARM_TIME = "@TIME";
        private const string PARM_TIMESTAMP = "@TIMESTAMP";

        #endregion

        #region SQL

        private const string INSERT_HNX_STOCKINFO = "INSERT INTO HNX_STOCKSINFO(TRADINGDATE, `TIME`, STOCKID, CODE, TOTALLISTING, REFERENCESTATUS, `STATUS`, REF, `OPEN`, `CLOSE`, HIGH, LOW, BESTOFFER, BESTBID, CEILING, FLOOR, TOTALOFFER, TOTALBID, BESTOFFERQTTY, BESTBIDQTTY, PRIORCLOSEPRICE, `MATCH`, MATCHQTTY, DATENO, OFFERCOUNT, BIDCOUNT, `AVERAGE`, TOTALTRADED, TOTALTRADEDVALUE, PTMATCH, PTMATCHQTTY, PTTOTALTRADED, PTTOTALTRADEDVALUE, BUYTRADINGQTTY, BUYCOUNT, BUYTRADINGVALUE, SELLTRADINGQTTY, SELLCOUNT, SELLTRADINGVALUE, TOTALTRADING, TOTALTRADINGVALUE, FBUY, FBUYVALUE, FSELL, FSELLVALUE, FREMAIN, FROOM, `TIMESTAMP`)     VALUES  (@TRADINGDATE, @TIME, @STOCKID, @CODE, @TOTALLISTING, @REFERENCESTATUS, @STATUS, @REF, @OPEN, @CLOSE, @HIGH, @LOW, @BESTOFFER, @BESTBID, @CEILING, @FLOOR, @TOTALOFFER, @TOTALBID, @BESTOFFERQTTY, @BESTBIDQTTY, @PRIORCLOSEPRICE, @MATCH, @MATCHQTTY, @DATENO, @OFFERCOUNT, @BIDCOUNT, @AVERAGE, @TOTALTRADED, @TOTALTRADEDVALUE, @PTMATCH, @PTMATCHQTTY, @PTTOTALTRADED, @PTTOTALTRADEDVALUE, @BUYTRADINGQTTY, @BUYCOUNT, @BUYTRADINGVALUE, @SELLTRADINGQTTY, @SELLCOUNT, @SELLTRADINGVALUE, @TOTALTRADING, @TOTALTRADINGVALUE, @FBUY, @FBUYVALUE, @FSELL, @FSELLVALUE, @FREMAIN, @FROOM, @TIMESTAMP)  ON DUPLICATE KEY UPDATE    TRADINGDATE=@TRADINGDATE, `TIME`=@TIME, STOCKID=@STOCKID, CODE=@CODE, TOTALLISTING=@TOTALLISTING, REFERENCESTATUS=@REFERENCESTATUS, `STATUS`=@STATUS, REF=@REF, `OPEN`=@OPEN, `CLOSE`=@CLOSE, HIGH=@HIGH, LOW=@LOW, BESTOFFER=@BESTOFFER, BESTBID=@BESTBID, CEILING=@CEILING, FLOOR=@FLOOR, TOTALOFFER=@TOTALOFFER, TOTALBID=@TOTALBID, BESTOFFERQTTY=@BESTOFFERQTTY, BESTBIDQTTY=@BESTBIDQTTY, PRIORCLOSEPRICE=@PRIORCLOSEPRICE, `MATCH`=@MATCH, MATCHQTTY=@MATCHQTTY, DATENO=@DATENO, OFFERCOUNT=@OFFERCOUNT, BIDCOUNT=@BIDCOUNT, AVERAGE=@AVERAGE, TOTALTRADED=@TOTALTRADED, TOTALTRADEDVALUE=@TOTALTRADEDVALUE, PTMATCH=@PTMATCH, PTMATCHQTTY=@PTMATCHQTTY, PTTOTALTRADED=@PTTOTALTRADED, PTTOTALTRADEDVALUE=@PTTOTALTRADEDVALUE, BUYTRADINGQTTY=@BUYTRADINGQTTY, BUYCOUNT=@BUYCOUNT, BUYTRADINGVALUE=@BUYTRADINGVALUE, SELLTRADINGQTTY=@SELLTRADINGQTTY, SELLCOUNT=@SELLCOUNT, SELLTRADINGVALUE=@SELLTRADINGVALUE, TOTALTRADING=@TOTALTRADING, TOTALTRADINGVALUE=@TOTALTRADINGVALUE, FBUY=@FBUY, FBUYVALUE=@FBUYVALUE, FSELL=@FSELL, FSELLVALUE=@FSELLVALUE, FREMAIN=@FREMAIN, FROOM=@FROOM, `TIMESTAMP`=@TIMESTAMP";

        private const string GET_HNX_ALLSTOCKSINFO = "SELECT * FROM HNX_STOCKSINFO WHERE DEAL_DATE=@DEAL_DATE";

        #endregion

        #region IHnxStock Members


        public List<HnxStockData> getHnxNewStockInfo(decimal timeStamp)
        {
            throw new NotImplementedException();
        }

        public List<HnxStockData> getHnxAllStockInfo()
        {
            throw new NotImplementedException();
        }

        public List<HnxStockData> countHnxStockInfo()
        {
            throw new NotImplementedException();
        }

        public void deleteHnxStockInfo()
        {
            throw new NotImplementedException();
        }

        public void insertHnxNewStockInfo(HnxStockData stock)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                    new MySqlParameter( PARM_TRADING_DATE,MySqlDbType.DateTime),
                    new MySqlParameter( PARM_TIME,MySqlDbType.VarChar),
                    new MySqlParameter( PARM_STOCK_ID,MySqlDbType.Int32),
                    new MySqlParameter( PARM_CODE,MySqlDbType.VarChar),
                    new MySqlParameter( PARM_TOTAL_LISTING_QTTY,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_REFERENCE_STATUS,MySqlDbType.VarChar),
                    new MySqlParameter( PARM_STATUS,MySqlDbType.VarChar),
                    new MySqlParameter( PARM_REF,MySqlDbType.Int32),
                    new MySqlParameter( PARM_OPEN_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_CLOSE_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_HIGHEST_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_LOWEST_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_BEST_OFFER_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_BEST_BID_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_CEILING_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_FLOOR_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_TOTAL_OFFER_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_TOTAL_BID_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_BEST_OFFER_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_BEST_BID_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_PRIOR_CLOSE_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_MATCH_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_MATCH_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_DATE_NO,MySqlDbType.Int32),
                    new MySqlParameter( PARM_OFFER_COUNT,MySqlDbType.Int64),
                    new MySqlParameter( PARM_BID_COUNT,MySqlDbType.Int64),
                    new MySqlParameter( PARM_AVERAGE_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_NM_TOTAL_TRADED_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_NM_TOTAL_TRADED_VALUE,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_PT_MATCH_PRICE,MySqlDbType.Int32),
                    new MySqlParameter( PARM_PT_MATCH_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_PT_TOTAL_TRADED_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_PT_TOTAL_TRADED_VALUE,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_TOTAL_BUY_TRADING_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_BUY_COUNT,MySqlDbType.Int64),
                    new MySqlParameter( PARM_TOTAL_BUY_TRADING_VALUE,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_TOTAL_SELL_TRADING_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_SELL_COUNT,MySqlDbType.Int64),
                    new MySqlParameter( PARM_TOTAL_SELL_TRADING_VALUE,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_TOTAL_TRADING_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_TOTAL_TRADING_VALUE,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_BUY_FOREIGN_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_BUY_FOREIGN_VALUE,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_SELL_FOREIGN_QTTY,MySqlDbType.Int64),
                    new MySqlParameter( PARM_SELL_FOREIGN_VALUE,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_REMAIN_FOREIGN_QTTY,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_CURRENT_ROOM,MySqlDbType.Decimal),
                    new MySqlParameter( PARM_TIMESTAMP,MySqlDbType.Int64)
                };

                parms[0].Value = stock.TradingDate;
                parms[1].Value = stock.Time;
                parms[2].Value = stock.StockId;
                parms[3].Value = stock.Code;
                parms[4].Value = stock.TotalListingQtty;
                parms[5].Value = stock.ReferenceStatus;
                parms[6].Value = stock.Status;
                parms[7].Value = stock.Ref;
                parms[8].Value = stock.Open;
                parms[9].Value = stock.Close;
                parms[10].Value = stock.High;
                parms[11].Value = stock.Low;
                parms[12].Value = stock.BestOffer;
                parms[13].Value = stock.BestBid;
                parms[14].Value = stock.Ceiling;
                parms[15].Value = stock.Floor;
                parms[16].Value = stock.TotalOffer;
                parms[17].Value = stock.TotalBid;
                parms[18].Value = stock.BestOfferQtty;
                parms[19].Value = stock.BestBidQtty;
                parms[20].Value = stock.PriorClosePrice;
                parms[21].Value = stock.MatchPrice;
                parms[22].Value = stock.MatchQtty;
                parms[23].Value = stock.DateNo;
                parms[24].Value = stock.OfferCount;
                parms[25].Value = stock.BidCount;
                parms[26].Value = stock.Avg;
                parms[27].Value = stock.TotalTradedQtty;
                parms[28].Value = stock.TotalTradedValue;
                parms[29].Value = stock.PtMatch;
                parms[30].Value = stock.PtMatchQtty;
                parms[31].Value = stock.PtTotalTradeQtty;
                parms[32].Value = stock.PtTotalTradeValue;
                parms[33].Value = stock.BuyTradingQtty;
                parms[34].Value = stock.BuyCount;
                parms[35].Value = stock.BuyTradingValue;
                parms[36].Value = stock.SellTradingQtty;
                parms[37].Value = stock.SellCount;
                parms[38].Value = stock.SellTradingValue;
                parms[39].Value = stock.TotalTradedQtty;
                parms[40].Value = stock.TotalTradedValue;
                parms[41].Value = stock.FBuy;
                parms[42].Value = stock.FBuyValue;
                parms[43].Value = stock.FSell;
                parms[44].Value = stock.FSellValue;
                parms[45].Value = stock.FRemain;
                parms[46].Value = stock.FRoom;
                parms[47].Value = DateTime.Now.Ticks;


                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, INSERT_HNX_STOCKINFO, parms);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
